
package com.feib.soeasy.service.impl;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.UUID;

import javax.annotation.Resource;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.PrintSetup;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import org.springframework.util.LinkedCaseInsensitiveMap;

import com.feib.soeasy.dao.CodeTableDao;
import com.feib.soeasy.dao.CommissionCodeTableDao;
import com.feib.soeasy.dao.Soez5006Dao;
import com.feib.soeasy.model.Group;
import com.feib.soeasy.model.Payment;
import com.feib.soeasy.model.RealAccount;
import com.feib.soeasy.model.WriteOffPayment;
import com.feib.soeasy.pagination.PaginatedList;
import com.feib.soeasy.service.Soez5006Service;
import com.feib.soeasy.util.CodeTableUtil;
import com.feib.soeasy.util.DateUtil;
import com.feib.soeasy.util.SoezListBean;
import com.feib.soeasy.util.UserUtil;


/**
 * @title (#)Soez5006ServiceImpl.java<br>
 * @description <br>
 * @author Jimmy Liu<br>
 * @version 1.0.0 2010/11/25
 * @copyright Far Eastern International Bank Copyright (c) 2010<br>
 * @2011/3/14 create by Jimmy Liu<br>
 */
@Service("soez5006Service")
public class Soez5006ServiceImpl extends BaseServiceImpl implements Soez5006Service {

	private static NumberFormat numberFormat = new DecimalFormat("##,###");
	protected Logger logger = LoggerFactory.getLogger(this.getClass());


	@Resource(name="soez5006Dao")
	private Soez5006Dao soez5006Dao;

	@Resource(name="codeTableDao")
	private CodeTableDao codeTableDao;

	@Resource(name="commissionCodeTableDao")
	public CommissionCodeTableDao commissionCodeTableDao;

	/**
	 * 暫存目錄
	 */
	@Resource(name = "tempDirectory")
	private String tempDirectory;




	//2011.12 by Leo
	@Override
	public List queryStatisticsByGroup(Group group, Date qryDateFrom, Date qryDateTo)
	{
		List<Map<String, Object>> rds = soez5006Dao.queryStoreStatisticsByGroup(null == group? null:group.getGroupNo(), null, qryDateFrom, qryDateTo);

		List<SoezListBean> results = new ArrayList<SoezListBean>();
		for(Map<String, Object> rd : rds)
		{
			SoezListBean listBean = new SoezListBean();
			String groupNo = (String) rd.get("group_no");
			String groupName = (String) rd.get("group_name");
			String accountNo = (String) rd.get("account_no");
			Integer rdCount = (Integer) rd.get("rdCount");
			BigDecimal amount = (BigDecimal) rd.get("amount");
			BigDecimal realPayAmount = (BigDecimal) rd.get("real_pay_amount");

			Group gp = new Group();
			gp.setGroupNo(groupNo);
			gp.setGroupName(groupName);
			listBean.setGroup(gp);

			RealAccount account = new RealAccount();
			account.setAccountNo(accountNo);
			listBean.setRealAccount(account);

			listBean.setPayedCount(new Long(rdCount.longValue()));
			listBean.setPayedTotalAmount(new Long(amount.longValue()));
			listBean.setPayedTotalRealPayAmount(new Long(realPayAmount.longValue()));

			results.add(listBean);
		}

		return results;
	}




	@Override
	public String doAjaxQueryCommissionCodeXls(Group group, String channel, String commissionCode, Date qryDateFrom, Date qryDateTo )
	{
		List results = queryPaymentByChannelCommission(group, channel, commissionCode, qryDateFrom, qryDateTo);

		//串欄位表頭
		String[] titles = {"超商","CODE",
				"業者代號","業者名稱","繳費帳號" ,"客戶名稱", "客戶編號", "繳費日期" , "銷帳日期","代收金額"};

		Workbook wb = new HSSFWorkbook(); // 若要產生xlsx則使用 XSSFWorkbook 即可
		Map<String, CellStyle> styles = createStyles(wb);

		Sheet sheet = wb.createSheet("超商郵局代收統計明細表");
		PrintSetup printSetup = sheet.getPrintSetup();
		printSetup.setLandscape(true);
		sheet.setFitToPage(true);
		sheet.setHorizontallyCenter(true);
		sheet.setZoom(3, 4);
		sheet.setColumnWidth(0, 256*20);
		sheet.setColumnWidth(1, 256*25);
		sheet.setColumnWidth(2, 256*20);
		sheet.setColumnWidth(3, 256*10);
		sheet.setColumnWidth(4, 256*10);
		sheet.setColumnWidth(5, 256*12);
		sheet.setColumnWidth(6, 256*12);
		sheet.setColumnWidth(7, 256*20);


		// 行數
		int rownum = 0;

		/* 效能提升  超商／郵局傳輸統計查詢  總計記數 */
		Long totalCount = new Long(0);
		Long totalAmount = new Long(0);



		//title row
		Row titleRow = sheet.createRow(rownum++);
		titleRow.setHeightInPoints((float)32.25);
		Cell titleCell = titleRow.createCell(0);
		titleCell.setCellValue("超商／郵局代收統計明細表");
		titleCell.setCellStyle(styles.get("title"));
		sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$J$1"));

		Row blankRow = sheet.createRow(rownum++);
		blankRow.setHeightInPoints((float)18);

		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");

		Row headerRow = sheet.createRow(rownum++);
		headerRow.setHeightInPoints((float) 25.5);
		Cell headerCell;
		for (int i = 0; i < titles.length; i++) {
			headerCell = headerRow.createCell(i);
			headerCell.setCellValue(titles[i]);
			headerCell.setCellStyle(styles.get("header"));
		}

		int no = 1;
		for(Iterator i=results.iterator();i.hasNext();)
		{
			/* 效能提升  超商／郵局傳輸統計查詢   總計記數 個別Code統計值 */
			Long codeCount = new Long(0);
			Long codeAmount = new Long(0);

			codeCount++;			

			Payment bean = (Payment)i.next();
			long txamt = bean.getWriteOffPayment().getAmount();  
			codeAmount += txamt;


			Row dataRow = sheet.createRow(rownum++);
			Cell dataCell;

			dataCell = dataRow.createCell(0);
			dataCell.setCellValue(CodeTableUtil.getInstance().getCodeName(CodeTableUtil.CODE_KIND_CHANNEL, bean.getWriteOffPayment().getChannel()));
			dataCell.setCellStyle(styles.get("cell"));

			dataCell = dataRow.createCell(1);
			String barcodeStores= bean.getBarcodeStores1();
			if(null!=barcodeStores){
				dataCell.setCellValue(barcodeStores.substring(6, 9));
			}

			dataCell.setCellStyle(styles.get("cell"));
			dataCell = dataRow.createCell(2);
			dataCell.setCellValue(bean.getGroup().getGroupNo());
			dataCell.setCellStyle(styles.get("cell"));
			dataCell = dataRow.createCell(3);
			dataCell.setCellValue(bean.getGroup().getGroupName());
			dataCell.setCellStyle(styles.get("cell"));
			dataCell = dataRow.createCell(4);
			dataCell.setCellValue(bean.getVirtualAccount());
			dataCell.setCellStyle(styles.get("cell"));
			dataCell = dataRow.createCell(5);
			dataCell.setCellValue(bean.getPayerName());
			dataCell.setCellStyle(styles.get("cell"));
			dataCell = dataRow.createCell(6);
			dataCell.setCellValue(bean.getPayerId());
			dataCell.setCellStyle(styles.get("cell"));
			dataCell = dataRow.createCell(7);
			Date  txDate = bean.getWriteOffPayment().getTxDate();
			if(null !=txDate)
				dataCell.setCellValue(dateFormat.format(txDate));
			dataCell.setCellStyle(styles.get("cell"));
			dataCell = dataRow.createCell(8);
			Date  writeOffDate = bean.getWriteOffPayment().getWriteOffDate();
			if(null !=txDate)
				dataCell.setCellValue(dateFormat.format(writeOffDate));
			dataCell.setCellValue(dateFormat.format(bean.getWriteOffPayment().getWriteOffDate()));
			dataCell.setCellStyle(styles.get("cell"));
			dataCell = dataRow.createCell(9);
			dataCell.setCellValue(txamt);
			dataCell.setCellStyle(styles.get("cell_number"));
			no++;
			/* 效能提升  超商／郵局傳輸統計查詢  總計記數 */
			totalCount += codeCount;
			totalAmount += codeAmount;

		}

		Row dataRow = sheet.createRow(rownum++);
		Cell dataCell;

		dataCell = dataRow.createCell(0);
		dataCell.setCellStyle(styles.get("cell"));
		dataCell = dataRow.createCell(1);				
		dataCell.setCellStyle(styles.get("cell"));	
		dataCell = dataRow.createCell(2);						
		dataCell.setCellStyle(styles.get("cell"));
		dataCell = dataRow.createCell(3);						
		dataCell.setCellStyle(styles.get("cell"));
		dataCell = dataRow.createCell(4);						
		dataCell.setCellStyle(styles.get("cell"));
		dataCell = dataRow.createCell(5);						
		dataCell.setCellStyle(styles.get("cell"));
		dataCell = dataRow.createCell(6);						
		dataCell.setCellStyle(styles.get("cell"));

		/* 效能提升  超商／郵局傳輸統計查詢  總計記數 */
		dataCell = dataRow.createCell(7);						
		dataCell.setCellStyle(styles.get("cell"));
		dataCell.setCellValue("總計");
		dataCell = dataRow.createCell(8);
		dataCell.setCellValue(totalCount+"筆");
		dataCell.setCellStyle(styles.get("cell_ALIGN_RIGHT"));
		dataCell = dataRow.createCell(9);
		dataCell.setCellValue(totalAmount);
		dataCell.setCellStyle(styles.get("cell_number"));



		int colCount = 0;
		/* 效能提升  超商／郵局傳輸統計查詢  總計記數 */
		while (colCount <= titles.length) {
			sheet.autoSizeColumn((short) colCount++,true);//自動調整欄寬
		}

		String tempDownloadFilePath = tempDirectory + "/" + UserUtil.getUserGroup().getGroupNo() + "/" + UserUtil.getUserId() + "/";
		File directory = new File(tempDownloadFilePath);             
		if(!directory.exists())
		{
			try {
				FileUtils.forceMkdir(directory);
			}
			catch (IOException e) {
				logger.error("{}",e);
			}
		}

		String fileFullName = tempDownloadFilePath + UUID.randomUUID().toString() + "-帳單通路統計明細表.xls";

		FileOutputStream out;
		try {
			out = new FileOutputStream(fileFullName);
			wb.write(out);
			out.close();
		}
		catch (FileNotFoundException e) {
			logger.error("{}",e);
		}
		catch (IOException e) {
			logger.error("{}",e);
		}
		return fileFullName;
	}

	@Override
	public String doExportStatisticsByGroupXls(Group group, Date qryDateFrom, Date qryDateTo)
	{
		List results = queryStatisticsByGroup(group, qryDateFrom, qryDateTo);

		//串欄位表頭
		String[] titles = {
				"No.","業者代號","入帳帳號" ,"業者名稱", "代收筆數", "代收金額" , "實撥金額"};

		Workbook wb = new HSSFWorkbook(); // 若要產生xlsx則使用 XSSFWorkbook 即可
		Map<String, CellStyle> styles = createStyles(wb);

		Sheet sheet = wb.createSheet("超商郵局代收統計表");
		PrintSetup printSetup = sheet.getPrintSetup();
		printSetup.setLandscape(true);
		sheet.setFitToPage(true);
		sheet.setHorizontallyCenter(true);
		sheet.setZoom(3, 4);
		sheet.setColumnWidth(1, 256*20);
		sheet.setColumnWidth(2, 256*20);
		sheet.setColumnWidth(3, 256*25);
		sheet.setColumnWidth(5, 256*20);
		sheet.setColumnWidth(6, 256*20);


		// 行數
		int rownum = 0;

		//title row
		Row titleRow = sheet.createRow(rownum++);
		titleRow.setHeightInPoints((float)32.25);
		Cell titleCell = titleRow.createCell(0);
		titleCell.setCellValue("超商／郵局代收統計表");
		titleCell.setCellStyle(styles.get("title"));
		sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$G$1"));

		Row blankRow = sheet.createRow(rownum++);
		blankRow.setHeightInPoints((float)18);

		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");

		Row headerRow = sheet.createRow(rownum++);
		headerRow.setHeightInPoints((float) 25.5);
		Cell headerCell;
		for (int i = 0; i < titles.length; i++) {
			headerCell = headerRow.createCell(i);
			headerCell.setCellValue(titles[i]);
			headerCell.setCellStyle(styles.get("header"));
		}
		int no = 1;
		for(Iterator i=results.iterator();i.hasNext();)
		{
			SoezListBean bean = (SoezListBean)i.next();

			Row dataRow = sheet.createRow(rownum++);
			Cell dataCell;
			dataCell = dataRow.createCell(0);
			dataCell.setCellValue(String.valueOf(no++));
			dataCell.setCellStyle(styles.get("cell"));
			dataCell = dataRow.createCell(1);
			dataCell.setCellValue(bean.getGroup().getGroupNo());
			dataCell.setCellStyle(styles.get("cell"));
			dataCell = dataRow.createCell(2);
			dataCell.setCellValue(bean.getRealAccount().getAccountNo());
			dataCell.setCellStyle(styles.get("cell"));
			dataCell = dataRow.createCell(3);
			dataCell.setCellValue(bean.getGroup().getGroupName());
			dataCell.setCellStyle(styles.get("cell"));
			dataCell = dataRow.createCell(4);
			dataCell.setCellValue(bean.getPayedCount());
			dataCell.setCellStyle(styles.get("cell"));
			dataCell = dataRow.createCell(5);
			dataCell.setCellValue(bean.getPayedTotalAmount());
			dataCell.setCellStyle(styles.get("cell_number"));
			dataCell = dataRow.createCell(6);
			dataCell.setCellValue(bean.getPayedTotalRealPayAmount());
			dataCell.setCellStyle(styles.get("cell_number"));

		}


		String tempDownloadFilePath = tempDirectory + "/" + UserUtil.getUserGroup().getGroupNo() + "/" + UserUtil.getUserId() + "/";
		File directory = new File(tempDownloadFilePath);             
		if(!directory.exists())
		{
			try {
				FileUtils.forceMkdir(directory);
			}
			catch (IOException e) {
				logger.error("{}",e);
			}
		}

		String fileFullName = tempDownloadFilePath + UUID.randomUUID().toString() + "-帳單通路統計表.xls";

		FileOutputStream out;
		try {
			out = new FileOutputStream(fileFullName);
			wb.write(out);
			out.close();
		}
		catch (FileNotFoundException e) {
			logger.error("{}",e);
		}
		catch (IOException e) {
			logger.error("{}",e);
		}
		return fileFullName;

	}

	public String doExportStatisticsXls(Group group, String[] channels, Date[] dateFroms, Date[] dateTos)
	{
		Workbook wb = new HSSFWorkbook(); // 若要產生xlsx則使用 XSSFWorkbook 即可
		Map<String, CellStyle> styles = createStyles(wb);

		Sheet sheet = wb.createSheet("超商郵局代收統計表");
		PrintSetup printSetup = sheet.getPrintSetup();
		printSetup.setLandscape(true);
		sheet.setFitToPage(true);
		sheet.setHorizontallyCenter(true);
		sheet.setZoom(3, 4);
		sheet.setColumnWidth(0, 256*30);
		sheet.setColumnWidth(1, 256*15);
		sheet.setColumnWidth(3, 256*40);
		sheet.setColumnWidth(5, 256*20);
		sheet.setColumnWidth(6, 256*20);


		// 行數
		int rownum = 0;

		//title row
		Row titleRow = sheet.createRow(rownum++);
		titleRow.setHeightInPoints((float)32.25);
		Cell titleCell = titleRow.createCell(0);
		titleCell.setCellValue("超商／郵局代收統計表");
		titleCell.setCellStyle(styles.get("title"));
		sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$G$1"));

		Row blankRow = sheet.createRow(rownum++);
		blankRow.setHeightInPoints((float)18);

		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");

		for(int i=0;i<channels.length;i++)
		{
			String channel = channels[i];
			Date dateFrom = dateFroms[i];
			Date dateTo = dateTos[i];
			if(channel != null && !"".equals(channel))
			{
				Row dataRow = sheet.createRow(rownum++);
				Cell dataCell;
				dataCell = dataRow.createCell(0);
				dataCell.setCellValue(codeTableDao.getCodeTable("CHANEL", channel).getCodeName() + "：" + dateFormat.format(dateFrom)+"～" + dateFormat.format(dateTo));
				dataCell.setCellStyle(styles.get("title2"));
				sheet.addMergedRegion(CellRangeAddress.valueOf("$A$"+rownum+":$G$" + rownum));
			}

		}


		//串欄位表頭
		String[] titles = {
				"日期", "超商／郵局", "CODE", "業者", "代收筆數", "代收金額", "實撥金額"};
		

		Row headerRow = sheet.createRow(rownum++);
		headerRow.setHeightInPoints((float) 25.5);
		Cell headerCell;



		for (int i = 0; i < titles.length; i++) {
			headerCell = headerRow.createCell(i);
			headerCell.setCellValue(titles[i]);
			headerCell.setCellStyle(styles.get("header"));
		}

		Long mainCount = new Long(0);
		Long mainTotalAmount = new Long(0);
		Long mainRealAmount = new Long(0);
		String cellRangeAddress = null;

		for(int i=0;i<channels.length;i++)
		{
			String channel = channels[i];
			if (channel == null) continue;

			Date dateFrom = dateFroms[i];
			Date dateTo = dateTos[i];
			int start = 0;
			List<String> commissionCodes = soez5006Dao.queryStoreCommissionCode(group, channel, dateFrom, dateTo);
			if(CollectionUtils.isEmpty(commissionCodes))
			{
				if(channel != null && !"".equals(channel))
				{
					Row dataRow = sheet.createRow(rownum++);
					Cell dataCell;
					dataCell = dataRow.createCell(0);
					dataCell.setCellValue(dateFormat.format(dateFrom)+"～" + dateFormat.format(dateTo));
					dataCell.setCellStyle(styles.get("cell"));
					dataCell = dataRow.createCell(1);
					dataCell.setCellValue(codeTableDao.getCodeTable("CHANEL", channel).getCodeName());
					dataCell.setCellStyle(styles.get("cell"));
					sheet.addMergedRegion(CellRangeAddress.valueOf("$C$"+rownum+":$G$" + rownum));					
					dataCell = dataRow.createCell(2);
					dataCell.setCellValue("查無資料 ");
					dataCell.setCellStyle(styles.get("cell"));
					dataCell = dataRow.createCell(3);				
					dataCell.setCellStyle(styles.get("cell"));
					dataCell = dataRow.createCell(4);				
					dataCell.setCellStyle(styles.get("cell"));
					dataCell = dataRow.createCell(5);				
					dataCell.setCellStyle(styles.get("cell"));
					dataCell = dataRow.createCell(6);				
					dataCell.setCellStyle(styles.get("cell"));
				}
			}
			else
			{
				start = rownum + 1;
				for(String commissionCode : commissionCodes)
				{
					if(channel != null && !channel.equals("H"))
					{
						List<Map<String, Object>> rds = this.soez5006Dao.queryStoreStatistics(group, channel, commissionCode, dateFrom, dateTo);
						if(rds != null && rds.size() >0)
						{
							int commissionStart = rownum + 1;
							for(Map<String, Object> rd : rds)
							{
								String groupName = (String) rd.get("group_name");
								Integer rdCount = (Integer) rd.get("rdCount");
								BigDecimal amount = (BigDecimal) rd.get("amount");
								BigDecimal realPayAmount = (BigDecimal) rd.get("real_pay_amount");

								Row dataRow = sheet.createRow(rownum++);
								Cell dataCell;
								dataCell = dataRow.createCell(0);
								dataCell.setCellValue(dateFormat.format(dateFrom)+"～" + dateFormat.format(dateTo));
								dataCell.setCellStyle(styles.get("cell"));
								dataCell = dataRow.createCell(1);
								dataCell.setCellValue(codeTableDao.getCodeTable("CHANEL", channel).getCodeName());
								dataCell.setCellStyle(styles.get("cell"));
								dataCell = dataRow.createCell(2);
								dataCell.setCellValue(commissionCode);
								dataCell.setCellStyle(styles.get("cell"));
								dataCell = dataRow.createCell(3);
								dataCell.setCellValue(groupName);
								dataCell.setCellStyle(styles.get("cell"));
								dataCell = dataRow.createCell(4);
								dataCell.setCellValue(rdCount);
								dataCell.setCellStyle(styles.get("cell"));
								dataCell = dataRow.createCell(5);
								dataCell.setCellValue(amount.longValue());
								dataCell.setCellStyle(styles.get("cell_number"));
								dataCell = dataRow.createCell(6);
								dataCell.setCellValue(realPayAmount.longValue());
								dataCell.setCellStyle(styles.get("cell_number"));

							}
							sheet.addMergedRegion(CellRangeAddress.valueOf("$C$"+commissionStart+":$C$" + rownum));

							// free resource
							rds.clear();
							rds = null;
						}
					}
				}

				List<Map<String, Object>> rds = this.soez5006Dao.queryStoreStatistics(group, channel, null, dateFrom, dateTo); //查全部的 commission, 以帳單業者角度
				Long count = new Long(0);
				Long totalAmount = new Long(0);
				Long totalRealPayAmount = new Long(0);
				for(Map<String, Object> rd : rds)
				{
					String groupName = (String) rd.get("group_name");
					Integer rdCount = (Integer) rd.get("rdCount");
					BigDecimal amount = (BigDecimal) rd.get("amount");
					BigDecimal realPayAmount = (BigDecimal) rd.get("real_pay_amount");


					Row dataRow = sheet.createRow(rownum++);
					Cell dataCell;
					dataCell = dataRow.createCell(0);
					dataCell.setCellValue(dateFormat.format(dateFrom)+"～" + dateFormat.format(dateTo));
					dataCell.setCellStyle(styles.get("cell"));
					dataCell = dataRow.createCell(1);
					dataCell.setCellValue(codeTableDao.getCodeTable("CHANEL", channel).getCodeName());
					dataCell.setCellStyle(styles.get("cell"));

					dataCell = dataRow.createCell(2);
					dataCell.setCellValue(groupName);
					dataCell.setCellStyle(styles.get("cell"));
					sheet.addMergedRegion(CellRangeAddress.valueOf("$C$"+rownum+":$D$" + rownum));
					dataCell = dataRow.createCell(3);				
					dataCell.setCellStyle(styles.get("cell"));
					dataCell = dataRow.createCell(4);
					dataCell.setCellValue(rdCount);
					dataCell.setCellStyle(styles.get("cell"));
					dataCell = dataRow.createCell(5);
					/*if(!channel.equals("H"))
						dataCell.setCellValue(bean.getPayedTotalAmount());
					else
						dataCell.setCellValue(bean.getPayedTotalRealPayAmount());
					 */
					dataCell.setCellValue(amount.longValue());


					dataCell.setCellStyle(styles.get("cell_number"));
					dataCell = dataRow.createCell(6);
					dataCell.setCellValue(realPayAmount.longValue());
					dataCell.setCellStyle(styles.get("cell_number"));

					count = count + rdCount;

					totalAmount = totalAmount + amount.longValue();
					totalRealPayAmount = totalRealPayAmount + realPayAmount.longValue();

				}

				Row dataRow = sheet.createRow(rownum++);
				Cell dataCell;
				dataCell = dataRow.createCell(0);
				dataCell.setCellValue(dateFormat.format(dateFrom)+"～" + dateFormat.format(dateTo));
				dataCell.setCellStyle(styles.get("cell"));
				dataCell = dataRow.createCell(1);
				dataCell.setCellValue(codeTableDao.getCodeTable("CHANEL", channel).getCodeName());				
				dataCell.setCellStyle(styles.get("cell"));
				dataCell = dataRow.createCell(2);				
				dataCell.setCellValue("統計");
				dataCell.setCellStyle(styles.get("cell"));
				sheet.addMergedRegion(CellRangeAddress.valueOf("$C$"+rownum+":$D$" + rownum));
				dataCell = dataRow.createCell(3);				
				dataCell.setCellStyle(styles.get("cell"));
				dataCell = dataRow.createCell(4);
				dataCell.setCellValue(count);
				dataCell.setCellStyle(styles.get("cell"));
				dataCell = dataRow.createCell(5);
				dataCell.setCellValue(totalAmount);
				dataCell.setCellStyle(styles.get("cell_number"));
				dataCell = dataRow.createCell(6);
				dataCell.setCellValue(totalRealPayAmount);
				dataCell.setCellStyle(styles.get("cell_number"));


				mainCount = mainCount + count;
				mainRealAmount = mainRealAmount + totalRealPayAmount;
				mainTotalAmount = mainTotalAmount + totalAmount;

				sheet.addMergedRegion(CellRangeAddress.valueOf("$A$"+start+":$A$" + rownum));
				sheet.addMergedRegion(CellRangeAddress.valueOf("$B$"+start+":$B$" + rownum));
			}



			//sheet.addMergedRegion(CellRangeAddress.valueOf(cellRangeAddress + ":$A$" + rownum));
		}

		Row dataRow = sheet.createRow(rownum++);
		Cell dataCell;
		sheet.addMergedRegion(CellRangeAddress.valueOf("$A$"+rownum+":$B$" + rownum));
		dataCell = dataRow.createCell(0);
		dataCell.setCellValue("總計");
		dataCell.setCellStyle(styles.get("cell"));
		dataCell = dataRow.createCell(1);				
		dataCell.setCellStyle(styles.get("cell"));

		sheet.addMergedRegion(CellRangeAddress.valueOf("$C$"+rownum+":$D$" + rownum));

		dataCell = dataRow.createCell(2);				
		dataCell.setCellValue("超商／郵局合計");
		dataCell.setCellStyle(styles.get("cell"));
		dataCell = dataRow.createCell(3);				
		dataCell.setCellStyle(styles.get("cell"));
		dataCell = dataRow.createCell(4);
		dataCell.setCellValue(mainCount);
		dataCell.setCellStyle(styles.get("cell"));
		dataCell = dataRow.createCell(5);
		dataCell.setCellValue(mainTotalAmount);
		dataCell.setCellStyle(styles.get("cell_number"));
		dataCell = dataRow.createCell(6);
		dataCell.setCellValue(mainRealAmount);
		dataCell.setCellStyle(styles.get("cell_number"));

		String tempDownloadFilePath = tempDirectory + "/" + UserUtil.getUserGroup().getGroupNo() + "/" + UserUtil.getUserId() + "/";
		File directory = new File(tempDownloadFilePath);             
		if(!directory.exists())
		{
			try {
				FileUtils.forceMkdir(directory);
			}
			catch (IOException e) {
				logger.error("{}",e);
			}
		}

		String fileFullName = tempDownloadFilePath + UUID.randomUUID().toString() + "-帳單通路統計表.xls";

		FileOutputStream out;
		try {
			out = new FileOutputStream(fileFullName);
			wb.write(out);
			out.close();
		}
		catch (FileNotFoundException e) {
			logger.error("{}",e);
		}
		catch (IOException e) {
			logger.error("{}",e);
		}
		return fileFullName;
	}


	/**
	 * Create a library of cell styles
	 */
	private static Map<String, CellStyle> createStyles(Workbook wb){
		Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
		CellStyle style;
		Font titleFont = wb.createFont();
		titleFont.setFontName("標楷體");
		titleFont.setFontHeightInPoints((short)24);
		style = wb.createCellStyle();
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		style.setFont(titleFont);
		styles.put("title", style);

		Font title2Font = wb.createFont();
		title2Font.setFontName("標楷體");
		//2011/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題
		title2Font.setFontHeightInPoints((short)12);
		style = wb.createCellStyle();
		style.setFont(title2Font);        
		style.setWrapText(false);//設定文字是否換行
		//2011/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
		style.setAlignment(CellStyle.ALIGN_LEFT);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		styles.put("title2", style);

		Font headerFont = wb.createFont();
		headerFont.setFontName("標楷體");
		headerFont.setFontHeightInPoints((short)12);
		style = wb.createCellStyle();
		style.setFont(headerFont);
		//2011/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
		style.setWrapText(false);//設定文字是否換行
		//2011/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		styles.put("header", style);

		Font cellFont = wb.createFont();
		cellFont.setFontName("標楷體");
		cellFont.setFontHeightInPoints((short)12);
		style = wb.createCellStyle();
		style.setFont(cellFont);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		//2011/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
		style.setWrapText(false);//設定文字是否換行
		//2011/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		styles.put("cell", style);

		style = wb.createCellStyle();
		style.setFont(cellFont);
		style.setAlignment(CellStyle.ALIGN_RIGHT);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		style.setWrapText(false);//設定文字是否換行
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		styles.put("cell_ALIGN_RIGHT", style);

		style = wb.createCellStyle();
		Font cellNumberFont = wb.createFont();
		cellNumberFont.setFontName("Arial");
		cellNumberFont.setFontHeightInPoints((short)14);
		style.setFont(cellNumberFont);
		style.setAlignment(CellStyle.ALIGN_RIGHT);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		//2011/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
		style.setWrapText(false);//設定文字是否換行
		//2011/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		style.setDataFormat(wb.createDataFormat().getFormat("$#,##0"));
		styles.put("cell_number", style);

		style = wb.createCellStyle();
		Font cellDateFont = wb.createFont();
		cellDateFont.setFontName("Arial");
		cellDateFont.setFontHeightInPoints((short)12);
		style.setFont(cellDateFont);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		//2011/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
		style.setWrapText(false);//設定文字是否換行
		//2011/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		style.setDataFormat(wb.createDataFormat().getFormat("yyyy/MM/dd"));
		styles.put("cell_date", style);


		cellFont = wb.createFont();
		cellFont.setFontName("標楷體");
		cellFont.setFontHeightInPoints((short)18);
		cellFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		style = wb.createCellStyle();
		style.setFont(cellFont);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		//2011/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
		style.setWrapText(false);//設定文字是否換行
		//2011/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		styles.put("cell_sub_total", style);

		style = wb.createCellStyle();
		cellNumberFont = wb.createFont();
		cellNumberFont.setFontName("Arial");
		cellNumberFont.setFontHeightInPoints((short)14);
		cellNumberFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		style.setFont(cellNumberFont);
		style.setAlignment(CellStyle.ALIGN_RIGHT);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		//2011/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
		style.setWrapText(false);//設定文字是否換行
		//2011/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		style.setDataFormat(wb.createDataFormat().getFormat("$#,##0"));
		styles.put("cell_number_sub_total", style);




		cellFont = wb.createFont();
		cellFont.setFontName("標楷體");
		cellFont.setFontHeightInPoints((short)14);
		cellFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		style = wb.createCellStyle();
		style.setFont(cellFont);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		//2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
		style.setWrapText(false);//設定文字是否換行
		//2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		styles.put("cell_total", style);

		style = wb.createCellStyle();
		cellNumberFont = wb.createFont();
		cellNumberFont.setFontName("Arial");
		cellNumberFont.setFontHeightInPoints((short)14);
		cellNumberFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		style.setFont(cellNumberFont);
		style.setAlignment(CellStyle.ALIGN_RIGHT);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		//2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
		style.setWrapText(false);//設定文字是否換行
		//2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		style.setDataFormat(wb.createDataFormat().getFormat("$#,##0"));
		styles.put("cell_number_total", style);





		return styles;
	}

	/* (non-Javadoc)
	 * @see com.feib.soeasy.service.Soez5006Service#doExportStatisticsHtml(com.feib.soeasy.model.Group, java.lang.Integer, java.util.Date, java.util.Date)
	 *TODO 化面查詢通路
	 */

	@Override
	public String doExportStatisticsHtml(Group group, String[] channels, Date[] dateFroms, Date[] dateTos)
	{

		if(null==group)
			group=new Group();

		StringBuffer html = new StringBuffer();
		//header row

		Long mainCount = new Long(0);
		Long mainTotalAmount = new Long(0);
		Long mainRealAmount = new Long(0);

		StringBuffer codeHtml = new StringBuffer();   
		
		boolean showNotFound=false;

		for(int i=0;i<channels.length;i++)
		{
			String channel = channels[i];
			Date dateFrom = dateFroms[i];
			Date dateTo = dateTos[i];
			StringBuffer codeHtmlSub = new StringBuffer(); 
			if(channel != null && !"".equals(channel))
			{
				//TODO 畫面調整速度
				List<String> commissionCodes = soez5006Dao.queryStoreCommissionCode(group, channel, dateFrom, dateTo);
				logger.debug("commissionCodes:{}", commissionCodes);
				if(CollectionUtils.isEmpty(commissionCodes))
				{   /* 完全找不到代號 但是有可能回傳郵局代號為null的資料*/
					SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");

					codeHtml.append("<tr>").append("\r\n");
					codeHtml.append("<td align='center'>").append("\r\n");
					codeHtml.append(dateFormat.format(dateFrom)).append("<br>～<br>").append(dateFormat.format(dateTo)).append("\r\n");
					codeHtml.append("</td>").append("\r\n");
					codeHtml.append("<td align='center'>").append("\r\n");
					codeHtml.append(codeTableDao.getCodeTable("CHANEL", channel).getCodeName()).append("\r\n");

					codeHtml.append("</td>").append("\r\n");
					// 
					codeHtml.append("<td colspan='6' align='center'>查無資料").append("\r\n");
					codeHtml.append("</td>").append("\r\n");
					codeHtml.append("<tr>").append("\r\n");

				}
				else
				{
					int smRowspan = 0;
					int cisCodeRowspan = 0;
					boolean first = true;
					StringBuffer bodyHtml = new StringBuffer();
					if(!channel.equals("H"))
					{
						// 超商, 非郵局, 郵局不用分commission, 只算統計即可
						for(String commissionCode : commissionCodes)
						{    						
							logger.debug("commissionCode:{}", commissionCode);
							
							// 取得單個commisionCode 的資料
							List<Map<String, Object>> rds = this.soez5006Dao.queryStoreStatistics(group, channel, commissionCode, dateFrom, dateTo);
							if(CollectionUtils.isNotEmpty(rds))
							{
								smRowspan = smRowspan + rds.size();
								cisCodeRowspan = rds.size();
								if(!first)
								{
									bodyHtml.append("<tr>").append("\r\n");
								}
								bodyHtml.append("<td align='center' rowspan='").append(String.valueOf(cisCodeRowspan)).append("'>").append("\r\n");
								bodyHtml.append(commissionCode).append("\r\n");
								bodyHtml.append("</td>").append("\r\n");

								boolean view = true;

								for(Map<String, Object> rd : rds)
								{
									String groupName = (String) rd.get("group_name");
									Integer rdCount = (Integer) rd.get("rdCount");
									BigDecimal amount = (BigDecimal) rd.get("amount");
									BigDecimal realPayAmount = (BigDecimal) rd.get("real_pay_amount");

									bodyHtml.append("<td align='left'>").append("\r\n");
									bodyHtml.append(groupName).append("\r\n");
									bodyHtml.append("</td>").append("\r\n");
									bodyHtml.append("<td align='right'>").append("\r\n");
									bodyHtml.append(DateUtil.changeToComma(rdCount.longValue())).append("\r\n");
									bodyHtml.append("</td>").append("\r\n");
									bodyHtml.append("<td align='right'>").append("\r\n");
									bodyHtml.append(DateUtil.changeToComma(amount.longValue())).append("\r\n");
									bodyHtml.append("</td>").append("\r\n");
									bodyHtml.append("<td align='right'>").append("\r\n");
									bodyHtml.append(DateUtil.changeToComma(realPayAmount.longValue())).append("\r\n");
									bodyHtml.append("</td>").append("\r\n");

									if(view)
									{
										//
										SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
										String dateFormS = dateFormat.format(dateFrom);
										String dateFormE = dateFormat.format(dateTo);

										bodyHtml.append("<td align='right' rowspan='").append(String.valueOf(cisCodeRowspan)).append("'>").append("\r\n");
										bodyHtml.append("<div id='viewCommissionCodeImg-").append(channel).append(commissionCode).append(dateFormS).append(dateFormE)
										.append("'><a href=\"javascript: void(0)\" class='btnZoom'>查詢</a></div>");
										bodyHtml.append("</td>").append("\r\n");
										view = false;
									}

									bodyHtml.append("</tr>").append("\r\n");
									first = false;
								}

								// free resource
								rds.clear();
								rds = null;
							}
						
					  }
					}
					else
					{

						// 無 rds 資料
						smRowspan = smRowspan + 1;
					
					}
					List<Map<String, Object>> rds = this.soez5006Dao.queryStoreStatistics(group, channel, null, dateFrom, dateTo); //查全部的 commission, 以帳單業者角度
					smRowspan = smRowspan + rds.size();

					Long count = new Long(0);
					Long totalAmount = new Long(0);
					Long totalRealPayAmount = new Long(0);

					boolean view = true;
					SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
					if(!channel.equals("H") && CollectionUtils.isEmpty(rds))
					{
					///jeff mark	

				/*		codeHtml.append("<tr>").append("\r\n");
						codeHtml.append("<td align='center'>").append("\r\n");
						codeHtml.append(dateFormat.format(dateFrom)).append("<br>～<br>").append(dateFormat.format(dateTo)).append("\r\n");
						codeHtml.append("</td>").append("\r\n");
						codeHtml.append("<td align='center'>").append("\r\n");
						codeHtml.append(codeTableDao.getCodeTable("CHANEL", channel).getCodeName()).append("\r\n");

						codeHtml.append("</td>").append("\r\n");
						// 
						 
						
						codeHtml.append("<td colspan='6' align='center'>查無資料").append("\r\n");
						codeHtml.append("</td>").append("\r\n");
						codeHtml.append("<tr>").append("\r\n");
 */
					}
					else
					{
					for(Map<String, Object> rd : rds)
					{
						String groupName = (String) rd.get("group_name");
						Integer rdCount = (Integer) rd.get("rdCount");
						BigDecimal amount = (BigDecimal) rd.get("amount");
						BigDecimal realPayAmount = (BigDecimal) rd.get("real_pay_amount");

						bodyHtml.append("<tr>").append("\r\n");

						bodyHtml.append("<td align='left' colspan='2'>").append("\r\n");
						bodyHtml.append(groupName).append("\r\n");
						bodyHtml.append("</td>").append("\r\n");
						bodyHtml.append("<td align='right'>").append("\r\n");
						bodyHtml.append(DateUtil.changeToComma(rdCount.longValue())).append("\r\n");
						bodyHtml.append("</td>").append("\r\n");
						bodyHtml.append("<td align='right'>").append("\r\n");

						bodyHtml.append(DateUtil.changeToComma(amount.longValue())).append("\r\n");

						bodyHtml.append("</td>").append("\r\n");
						bodyHtml.append("<td align='right'>").append("\r\n");
						bodyHtml.append(DateUtil.changeToComma(realPayAmount.longValue())).append("\r\n");
						bodyHtml.append("</td>").append("\r\n");

						/*依據遠銀需求加入郵局可看明細資料，20120312, Jimmy*/
						if("H".equals(channel))
						{
							//
							if (view)
							{
								
								String dateFormS = dateFormat.format(dateFrom);
								String dateFormE = dateFormat.format(dateTo);

								bodyHtml.append("<td align='right' rowspan='").append(String.valueOf(rds.size())).append("'>").append("\r\n");
								bodyHtml.append("<div id='viewCommissionCodeImg-").append(channel).append("   ").append(dateFormS).append(dateFormE)
								.append("'><a href=\"javascript: void(0)\" class='btnZoom'>查詢</a></div>");
								bodyHtml.append("</td>").append("\r\n");
								bodyHtml.append("</tr>").append("\r\n");
								view = false;
							}
						}
						else
						{
							bodyHtml.append("<td align='right'></td>").append("\r\n");
							bodyHtml.append("</tr>").append("\r\n");
						}


						//總計
						count = count + rdCount.longValue();
						totalAmount = totalAmount + amount.longValue();
						totalRealPayAmount = totalRealPayAmount + realPayAmount.longValue();

					}

					smRowspan = smRowspan + 1;
					bodyHtml.append("<tr>").append("\r\n");
					bodyHtml.append("<td align='center' colspan='2'>").append("\r\n");
					bodyHtml.append("統計").append("\r\n");
					bodyHtml.append("</td>").append("\r\n");
					bodyHtml.append("<td align='right'>").append("\r\n");
					bodyHtml.append(DateUtil.changeToComma(count)).append("\r\n");
					bodyHtml.append("</td>").append("\r\n");
					bodyHtml.append("<td align='right'>").append("\r\n");
					bodyHtml.append(DateUtil.changeToComma(totalAmount)).append("\r\n");
					bodyHtml.append("</td>").append("\r\n");
					bodyHtml.append("<td align='right'>").append("\r\n");
					bodyHtml.append(DateUtil.changeToComma(totalRealPayAmount)).append("\r\n");
					bodyHtml.append("</td>").append("\r\n");
					/* TODO 先適用原本明細 不分業*/
					
					/* 統計應該為固定欄位*/
					if(count>0){
						String dateFormS = dateFormat.format(dateFrom);
						String dateFormE = dateFormat.format(dateTo);
						bodyHtml.append("<td align='right'>").append("\r\n");
						bodyHtml.append("<div id='viewChannel-").append(channel).append(dateFormS).append(dateFormE)
						.append("'><a href=\"javascript: void(0)\" class='btnZoom'>查詢</a></div>");
						bodyHtml.append("</td>").append("\r\n");
					}
					bodyHtml.append("</tr>").append("\r\n");

					}

					//=======================================================================

					codeHtmlSub.append("<tr>").append("\r\n");
					codeHtmlSub.append("<td align='center' rowspan='").append(String.valueOf(smRowspan)).append("'>").append("\r\n");
					codeHtmlSub.append(dateFormat.format(dateFrom)).append("<br>～<br>").append(dateFormat.format(dateTo)).append("\r\n");
					codeHtmlSub.append("</td>").append("\r\n");
					codeHtmlSub.append("<td align='center' rowspan='").append(String.valueOf(smRowspan)).append("'>").append("\r\n");
					codeHtmlSub.append(codeTableDao.getCodeTable("CHANEL", channel).getCodeName()).append("\r\n");

					codeHtmlSub.append("</td>").append("\r\n");

					codeHtmlSub.append(bodyHtml.toString()).append("\r\n");
					codeHtml.append(codeHtmlSub.toString());

					mainCount = mainCount + count;
					mainRealAmount = mainRealAmount + totalRealPayAmount;
					mainTotalAmount = mainTotalAmount + totalAmount;
				} //CollectionUtils.isnotEmpty(commissionCodes))      		

			}
		}

		//------------------------------------------------------------------------------------------------------------


		//串欄位表頭
		String[] titles = {
				"日期","超商／郵局","CODE" ,"業者", "代收筆數", "代收金額" , "實撥金額","明細"};

		html.append("<table id=\"listTable\">").append("\r\n");
		html.append("<thead><tr>").append("\r\n");

		for (int j = 0; j < titles.length; j++) {
			html.append("<th>").append(titles[j]).append("</th>").append("\r\n");
		}

		html.append("</tr></thead>").append("\r\n");
		html.append("<tbody>").append("\r\n");


		if(codeHtml.length() != 0)
		{
			html.append(codeHtml.toString()).append("\r\n");

			html.append("<tr>").append("\r\n");
			html.append("<td align='center' colspan='2'>").append("\r\n");
			html.append("總計").append("\r\n");
			html.append("</td>").append("\r\n");
			html.append("<td align='center' colspan='2'>").append("\r\n");
			html.append("超商／郵局合計").append("\r\n");
			html.append("</td>").append("\r\n");
			html.append("<td align='right'>").append("\r\n");
			html.append(DateUtil.changeToComma(mainCount)).append("\r\n");
			html.append("</td>").append("\r\n");
			html.append("<td align='right'>").append("\r\n");
			html.append(DateUtil.changeToComma(mainTotalAmount)).append("\r\n");
			html.append("</td>").append("\r\n");
			html.append("<td align='right'>").append("\r\n");
			html.append(DateUtil.changeToComma(mainRealAmount)).append("\r\n");
			html.append("</td>").append("\r\n");
			/* 總明細  有總計數目再顯示*/
			if(mainCount>0){	
				html.append("<td align='right' >").append("\r\n");
				html.append("<div id='viewTotal-1")
				.append("'><a href=\"javascript: void(0)\" class='btnZoom'>查詢</a></div>");
				html.append("</td>").append("\r\n");
			}
			html.append("</tr>").append("\r\n");
			// 下載按鈕
			html.append("<tr>").append("\r\n");
			html.append("<td colspan=\"8\" align=\"center\">")
			.append("<input type=\"button\" id=\"doDownloadExcelFileBtn\" value=\"下載(Excel)\" onclick=\"javascript:doDownloadExcelFileAction();\" />")
			.append(" <input type=\"button\" id=\"printBtn2\" value=\"列印\"  onclick=\"javascript:divPrint('queryResultDiv');\" />") 
			.append("</td>").append("\r\n");
			html.append("</tr>").append("\r\n");

			html.append("</tbody></table>").append("\r\n");

			html.append("<script type=\"text/javascript\">").append("\r\n");
			html.append("<!--").append("\r\n");
			html.append("$(document).ready(function() {").append("\r\n");
			html.append("$(\"[id^='viewCommissionCodeImg-']\").click(function() {").append("\r\n");

			html.append("var uid = this.id.substring(22);").append("\r\n");
			html.append("doQueryDetailAction(uid); ").append("\r\n");
			html.append("});").append("\r\n");
			html.append("$(\"[id^='viewChannel-']\").click(function() {").append("\r\n");
			html.append("var uid = this.id.substring(12);").append("\r\n");
			html.append("doQueryChannelAction(uid); ").append("\r\n");
			html.append("});").append("\r\n");
			html.append("$(\"[id^='viewTotal-']\").click(function() {").append("\r\n");
			html.append("var uid = this.id.substring(10);").append("\r\n");
			html.append("doQueryTotalAction(uid); ").append("\r\n");
			html.append("});").append("\r\n");

			html.append("});").append("\r\n");
			html.append("//-->").append("\r\n");
			html.append("</script>").append("\r\n");
			html.append("");	
		}
		else
		{
			html.append("<tr>").append("\r\n");
			html.append("<td colspan=\"8\" align=\"center\">")
			.append("無超商／郵局資料")
			.append("</td>").append("\r\n");
			html.append("</tr>").append("\r\n");
		}


		return html.toString();
	}

	/**
	 * @param html
	 * @param dbData
	 */
	private void writeHtmlData(StringBuffer html, Map<String, Map<String, List<Map<String, Object>>>> dbData) {
		//dbData 以時間做key的集合

		if (null != dbData && 0 != dbData.size())
		{
			boolean first = true;
			boolean innerfirst = true;
			//日期集合
			Set<String> dbDataKeys1=dbData.keySet();

			//通路
			String dbDataKeys[] = new String[]{WriteOffPayment.CHANNEL_7_11, WriteOffPayment.CHANNEL_FAMILY_MART
					, WriteOffPayment.CHANNEL_OK_MART,WriteOffPayment.CHANNEL_HILIFE };
			//
			for (String key1 :dbDataKeys1){    
				int count =0;
				for (String key :dbDataKeys){ 
					List<Map<String, Object>>  channelDatas = dbData.get(key1).get(key);
					if(null !=channelDatas && 0 != channelDatas.size())  {

						count+= channelDatas.size();
					}

				}

				for (String key :dbDataKeys){                    
					// List<Map<String, Object>> channelDatas=new  ArrayList<Map<String, Object>> ();
					List<Map<String, Object>>  channelDatas = dbData.get(key1).get(key);


					String writeOffDate = key1;



					if(null !=channelDatas && 0 != channelDatas.size())  {


						html.append("<tr>").append("\r\n");
						if (first)
						{
							html.append("<td rowspan=\"").append(count).append("\">")
							.append(writeOffDate.substring(0, 4)).append("/").append(writeOffDate.substring(4, 6)).append("/").append(writeOffDate.substring(6))
							.append("</td>");
							first = false;
						}  
						for(Map<String, Object> channelData : channelDatas)
						{

							String channelName = (String)channelData.get("CHANNEL_NAME");
							String commissionCode = (String)channelData.get("COMMISSION_CODE");
							String commissionCodeName = (String)channelData.get("COMMISSION_CODE_NAME");
							Long recordCount = (Long)channelData.get("RECORD_COUNT");
							Long amount = (Long)channelData.get("AMOUNT");
							Long commissionAmount = (Long)channelData.get("COMMISSION_AMOUNT");
							Long realAmount = (Long)channelData.get("REAL_AMOUNT");




							if (innerfirst)
							{
								html.append("<td rowspan=\"").append(channelDatas.size()).append("\">").append(channelName).append("</td>");
								innerfirst = false;
							}  

							// commissionCodewriteOffDate
							//      html.append("<td>").append(channelName).append("</td>").append("\r\n");
							html.append("<td>").append(commissionCode).append("</td>").append("\r\n");
							// 代收類別
							html.append("<td>").append(commissionCodeName).append("</td>").append("\r\n");
							if(commissionCode.equals("小計")){
								html.append("<td align=\"right\">").append("").append("</td>").append("\r\n");  
							}else{   // 代收手續費小計}
								html.append("<td align=\"right\">").append(numberFormat.format(commissionAmount)).append("</td>").append("\r\n");  
							}

							// 代收筆數小計
							html.append("<td align=\"right\">").append(numberFormat.format(recordCount)).append("</td>").append("\r\n");
							// 代收金額小計
							html.append("<td align=\"right\">").append(numberFormat.format(amount)).append("</td>").append("\r\n");

							// 實際入帳金額小計
							html.append("<td align=\"right\">").append(numberFormat.format(realAmount)).append("</td>").append("\r\n");

							html.append("</tr>").append("\r\n"); 
						} 
						innerfirst = true;

					}

				}
				first = true;
			}     
		}
	}


	@Override
	public List queryPaymentByChannelCommission(Group queryGroup, String channel, String commissionCode, Date qryDateFrom, Date qryDateTo ) 
	{
		if (null != qryDateFrom)
		{
			// 將時分秒設定在00:00:00
			qryDateFrom = DateUtil.getBeginOfDateTime(qryDateFrom);
		}

		if (null != qryDateTo)
		{
			// 將時分秒設定在23:59:59
			qryDateTo = DateUtil.getEndOfDateTime(qryDateTo);
		}

		List<Map<String, Object>> rds = this.soez5006Dao.queryPaymentByChannelCommission(queryGroup.getGroupNo(), channel, commissionCode, qryDateFrom, qryDateTo);

		if(rds == null || rds.size() == 0)
			return null;

		List<Payment> results = new ArrayList<Payment>();

		for(Map<String, Object> rd : rds)
		{
			String groupNo = (String) rd.get("group_no");
			String groupName = (String) rd.get("group_name");
			String virtualAccount = (String) rd.get("virtual_account");
			String payerName = (String) rd.get("payer_name");
			String payerId = (String) rd.get("payer_id");
			BigDecimal amount = (BigDecimal) rd.get("amount");
			Date txDate = (Date) rd.get("tx_date");
			Date writeOffDate = (Date) rd.get("write_off_date");
			String channelCode = channel;
			String barcdoe1 = (String) rd.get("barcdoe1");
			if("H".equals(channel)){
				barcdoe1=null;
			}

			Group group = new Group();
			Payment payment = new Payment();
			WriteOffPayment writeOffPayment = new WriteOffPayment();

			group.setGroupNo(groupNo);
			group.setGroupName(groupName);
			payment.setVirtualAccount(virtualAccount);
			payment.setPayerName(payerName);
			payment.setPayerId(payerId);
			payment.setBarcodeStores1(barcdoe1);//TODO
			writeOffPayment.setTxDate(txDate);
			writeOffPayment.setWriteOffDate(writeOffDate);
			writeOffPayment.setAmount(new Long((amount).longValue()));
			writeOffPayment.setChannel(channelCode);//TODO

			payment.setGroup(group);
			payment.setWriteOffPayment(writeOffPayment);
			results.add(payment);
		}



		return results;
	}


	/*取得單種代號明細  */
	public PaginatedList<Payment> queryGroupFeedbackLogByChannelCommission(
			Group queryGroup, String channel, String commissionCode, Date adayS, Date adayEo,PaginatedList<Map<String, Object>> paginationConfig) {
		if (null != adayS)
		{
			// 將時分秒設定在00:00:00
			adayS = DateUtil.getBeginOfDateTime(adayS);
		}

		if (null != adayEo)
		{
			// 將時分秒設定在23:59:59
			adayEo = DateUtil.getEndOfDateTime(adayEo);
		}



		PaginatedList<Map<String, Object>> fbLogs = soez5006Dao.queryPaymentByChannelCommission(queryGroup, channel, commissionCode, adayS, adayEo, paginationConfig);


		if(fbLogs.getPaginatedList()== null || fbLogs.getPaginatedList().size() == 0)
			return null;

		/* 設定複製用PaginatedList 再對資料作處理 不然 foreach的狀況下不能對來源檔案作操作  */
		PaginatedList<Payment> results = new PaginatedList<Payment>();
		List<Payment> p= new ArrayList<Payment>();
		results.setPaginatedList(p);
		results.setFullListSize(fbLogs.getFullListSize());
		results.setPageSize(fbLogs.getPageSize());
		results.setPageNumber(fbLogs.getPageNumber());
		results.setSortCriterion(fbLogs.getSortCriterion());
		results.setSortDirection(fbLogs.getSortDirection());

		for(Map<String, Object> fblog:fbLogs.getPaginatedList())
		{	

			String groupNo = (String) fblog.get("group_no");
			String groupName = (String) fblog.get("group_name");
			String virtualAccount = (String) fblog.get("virtual_account");
			String payerName = (String) fblog.get("payer_name");
			String payerId = (String) fblog.get("payer_id");
			BigDecimal amount = (BigDecimal) fblog.get("amount");
			Date txDate = (Date) fblog.get("tx_date");
			Date writeOffDate = (Date) fblog.get("write_off_date");
			String channelCode = channel;
			String barcdoe1 = (String) fblog.get("barcdoe1");
			if("H".equals(channel)){
				barcdoe1=null;
			}

			Group group = new Group();
			Payment payment = new Payment();
			WriteOffPayment writeOffPayment = new WriteOffPayment();

			group.setGroupNo(groupNo);
			group.setGroupName(groupName);
			payment.setVirtualAccount(virtualAccount);
			payment.setPayerName(payerName);
			payment.setPayerId(payerId);
			payment.setBarcodeStores1(barcdoe1);//TODO
			writeOffPayment.setTxDate(txDate);
			writeOffPayment.setWriteOffDate(writeOffDate);
			writeOffPayment.setAmount(new Long((amount).longValue()));
			writeOffPayment.setChannel(channelCode);//TODO

			payment.setGroup(group);
			payment.setWriteOffPayment(writeOffPayment);
			results.getPaginatedList().add(payment);

			/* 總計明細用的總計 顯示在最下方,最後一頁再顯示 */
			if(fbLogs.isLastPage()){	
				Map<String, Object> total=(Map<String, Object>) fbLogs.getTotalObject();
				fblog.put("totalCount", total.get("totalCount"));
				fblog.put("totalAmount", ((BigDecimal)total.get("totalAmount")).longValue());
				results.setTotalObject(fblog);
			}

		}



		fbLogs.getPaginatedList().clear();
		fbLogs=null;

		return results;
	}


	/*取得通路明細  */
	public PaginatedList<Payment> queryPaymentByChannel(
			Group group, String channels[],
			Date dateFroms[], Date dateTos[],PaginatedList<Map<String, Object>> paginationConfig) {
		/*
		 *	畫面上的總計放大鏡
		 *	應該要傳所查詢的通路跟各通路的時間
		 */

		if(null==group)
			group=new Group();

		if (!UserUtil.isBankGroup())
		{
			group=UserUtil.getUserGroup();
		}

		PaginatedList<Map<String, Object>> fbLogs = soez5006Dao.queryPaymentByChannel(group, channels, dateFroms, dateTos, paginationConfig);


		if(fbLogs.getPaginatedList()== null || fbLogs.getPaginatedList().size() == 0)
			return null;

		/* 設定複製用PaginatedList 再對資料作處理 不然 foreach的狀況下不能對來源檔案作操作  */
		PaginatedList<Payment> results = new PaginatedList<Payment>();
		List<Payment> p= new ArrayList<Payment>();
		results.setPaginatedList(p);
		results.setFullListSize(fbLogs.getFullListSize());
		results.setPageSize(fbLogs.getPageSize());
		results.setPageNumber(fbLogs.getPageNumber());
		results.setSortCriterion(fbLogs.getSortCriterion());
		results.setSortDirection(fbLogs.getSortDirection());

		for(Map<String, Object> fblog:fbLogs.getPaginatedList())
		{	

			String groupNo = (String) fblog.get("group_no");
			String groupName = (String) fblog.get("group_name");
			String virtualAccount = (String) fblog.get("virtual_account");
			String payerName = (String) fblog.get("payer_name");
			String payerId = (String) fblog.get("payer_id");
			BigDecimal amount = (BigDecimal) fblog.get("amount");
			Date txDate = (Date) fblog.get("tx_date");
			Date writeOffDate = (Date) fblog.get("write_off_date");
			String channelCode = (String) fblog.get("channel");;
			String barcdoe1 = (String) fblog.get("barcdoe1");
			if("H".equals(channelCode)){
				barcdoe1=null;
			}

			Group queryGroup = new Group();
			Payment payment = new Payment();
			WriteOffPayment writeOffPayment = new WriteOffPayment();

			queryGroup.setGroupNo(groupNo);
			queryGroup.setGroupName(groupName);
			payment.setVirtualAccount(virtualAccount);
			payment.setPayerName(payerName);
			payment.setPayerId(payerId);
			payment.setBarcodeStores1(barcdoe1);//TODO
			writeOffPayment.setTxDate(txDate);
			writeOffPayment.setWriteOffDate(writeOffDate);
			writeOffPayment.setAmount(new Long((amount).longValue()));
			writeOffPayment.setChannel(channelCode);//TODO

			payment.setGroup(queryGroup);
			payment.setWriteOffPayment(writeOffPayment);
			results.getPaginatedList().add(payment);


			/* 總計明細用的總計 顯示在最下方,最後一頁再顯示 */
			if(fbLogs.isLastPage()){	
				Map<String, Object> total=(Map<String, Object>) fbLogs.getTotalObject();
				fblog.put("totalCount", total.get("totalCount"));
				fblog.put("totalAmount", ((BigDecimal)total.get("totalAmount")).longValue());
				results.setTotalObject(fblog);
			}



		}


		fbLogs.getPaginatedList().clear();
		fbLogs=null;

		return results;
	}



	public String doExportChannelStatisticsXls(Group group, String[] channels, Date[] dateFroms, Date[] dateTos)
	{



		//串欄位表頭

		String[] titles = {"超商","CODE",
				"業者代號","業者名稱","繳費帳號" ,"客戶名稱", "客戶編號", "繳費日期" , "銷帳日期","代收金額"};


		Workbook wb = new HSSFWorkbook(); // 若要產生xlsx則使用 XSSFWorkbook 即可
		Map<String, CellStyle> styles = createStyles(wb);

		Sheet sheet = wb.createSheet("超商郵局代收統計明細表");
		PrintSetup printSetup = sheet.getPrintSetup();
		printSetup.setLandscape(true);
		sheet.setFitToPage(true);
		sheet.setHorizontallyCenter(true);
		sheet.setZoom(3, 4);
		sheet.setColumnWidth(0, 256*20);
		sheet.setColumnWidth(1, 256*25);
		sheet.setColumnWidth(2, 256*20);
		sheet.setColumnWidth(3, 256*10);
		sheet.setColumnWidth(4, 256*10);
		sheet.setColumnWidth(5, 256*12);
		sheet.setColumnWidth(6, 256*12);
		sheet.setColumnWidth(7, 256*20);


		// 行數
		int rownum = 0;

		/* 效能提升  超商／郵局傳輸統計查詢  總計記數 */
		Long totalCount = new Long(0);
		Long totalAmount = new Long(0);



		//title row
		Row titleRow = sheet.createRow(rownum++);
		titleRow.setHeightInPoints((float)32.25);
		Cell titleCell = titleRow.createCell(0);
		titleCell.setCellValue("超商／郵局代收統計明細表");
		titleCell.setCellStyle(styles.get("title"));
		sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$J$1"));

		Row blankRow = sheet.createRow(rownum++);
		blankRow.setHeightInPoints((float)18);

		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");

		Row headerRow = sheet.createRow(rownum++);
		headerRow.setHeightInPoints((float) 25.5);
		Cell headerCell;
		for (int i = 0; i < titles.length; i++) {
			headerCell = headerRow.createCell(i);
			headerCell.setCellValue(titles[i]);
			headerCell.setCellStyle(styles.get("header"));
		}




		for(int t=0;t<channels.length;t++)
		{
			String channel = channels[t];
			Date dateFrom = dateFroms[t];
			Date dateTo = dateTos[t];
			int start = 0;

			if("null".equalsIgnoreCase(channel) || StringUtils.isBlank(channel))
			{
			}else{
				List results = queryPaymentByChannelCommission(group, channel, null, dateFrom, dateTo);

				int no = 1;
				if( null!=results){
				for(Iterator i=results.iterator();i.hasNext();)
				{
					/* 效能提升  超商／郵局傳輸統計查詢   總計記數 個別Code統計值 */
					Long codeCount = new Long(0);
					Long codeAmount = new Long(0);

					codeCount++;			

					Payment bean = (Payment)i.next();
					long txamt = bean.getWriteOffPayment().getAmount();  
					codeAmount += txamt;


					Row dataRow = sheet.createRow(rownum++);
					Cell dataCell;

					dataCell = dataRow.createCell(0);
					dataCell.setCellValue(CodeTableUtil.getInstance().getCodeName(CodeTableUtil.CODE_KIND_CHANNEL, bean.getWriteOffPayment().getChannel()));
					dataCell.setCellStyle(styles.get("cell"));

					dataCell = dataRow.createCell(1);
					String barcodeStores= bean.getBarcodeStores1();
					if(null!=barcodeStores){
						dataCell.setCellValue(barcodeStores.substring(6, 9));
					}

					dataCell.setCellStyle(styles.get("cell"));
					dataCell = dataRow.createCell(2);
					dataCell.setCellValue(bean.getGroup().getGroupNo());
					dataCell.setCellStyle(styles.get("cell"));
					dataCell = dataRow.createCell(3);
					dataCell.setCellValue(bean.getGroup().getGroupName());
					dataCell.setCellStyle(styles.get("cell"));
					dataCell = dataRow.createCell(4);
					dataCell.setCellValue(bean.getVirtualAccount());
					dataCell.setCellStyle(styles.get("cell"));
					dataCell = dataRow.createCell(5);
					dataCell.setCellValue(bean.getPayerName());
					dataCell.setCellStyle(styles.get("cell"));
					dataCell = dataRow.createCell(6);
					dataCell.setCellValue(bean.getPayerId());
					dataCell.setCellStyle(styles.get("cell"));
					dataCell = dataRow.createCell(7);
					Date  txDate = bean.getWriteOffPayment().getTxDate();
					if(null !=txDate)
						dataCell.setCellValue(dateFormat.format(txDate));
					dataCell.setCellStyle(styles.get("cell"));
					dataCell = dataRow.createCell(8);
					Date  writeOffDate = bean.getWriteOffPayment().getWriteOffDate();
					if(null !=txDate)
						dataCell.setCellValue(dateFormat.format(writeOffDate));
					dataCell.setCellValue(dateFormat.format(bean.getWriteOffPayment().getWriteOffDate()));
					dataCell.setCellStyle(styles.get("cell"));
					dataCell = dataRow.createCell(9);
					dataCell.setCellValue(txamt);
					dataCell.setCellStyle(styles.get("cell_number"));
					no++;
					/* 效能提升  超商／郵局傳輸統計查詢  總計記數 */
					totalCount += codeCount;
					totalAmount += codeAmount;

				}
				}
			}
		}
		Row dataRow = sheet.createRow(rownum++);
		Cell dataCell;

		dataCell = dataRow.createCell(0);
		dataCell.setCellStyle(styles.get("cell"));
		dataCell = dataRow.createCell(1);				
		dataCell.setCellStyle(styles.get("cell"));	
		dataCell = dataRow.createCell(2);						
		dataCell.setCellStyle(styles.get("cell"));
		dataCell = dataRow.createCell(3);						
		dataCell.setCellStyle(styles.get("cell"));
		dataCell = dataRow.createCell(4);						
		dataCell.setCellStyle(styles.get("cell"));
		dataCell = dataRow.createCell(5);						
		dataCell.setCellStyle(styles.get("cell"));
		dataCell = dataRow.createCell(6);						
		dataCell.setCellStyle(styles.get("cell"));

		/* 效能提升  超商／郵局傳輸統計查詢  總計記數 */
		dataCell = dataRow.createCell(7);						
		dataCell.setCellStyle(styles.get("cell"));
		dataCell.setCellValue("總計");
		dataCell = dataRow.createCell(8);
		dataCell.setCellValue(totalCount+"筆");
		dataCell.setCellStyle(styles.get("cell_ALIGN_RIGHT"));
		dataCell = dataRow.createCell(9);
		dataCell.setCellValue(totalAmount);
		dataCell.setCellStyle(styles.get("cell_number"));



		int colCount = 0;
		/* 效能提升  超商／郵局傳輸統計查詢  總計記數 */
		while (colCount <= titles.length) {
			sheet.autoSizeColumn((short) colCount++,true);//自動調整欄寬
		}

		String tempDownloadFilePath = tempDirectory + "/" + UserUtil.getUserGroup().getGroupNo() + "/" + UserUtil.getUserId() + "/";
		File directory = new File(tempDownloadFilePath);             
		if(!directory.exists())
		{
			try {
				FileUtils.forceMkdir(directory);
			}
			catch (IOException e) {
				logger.error("{}",e);
			}
		}

		String fileFullName = tempDownloadFilePath + UUID.randomUUID().toString() + "-帳單通路統計明細表.xls";

		FileOutputStream out;
		try {
			out = new FileOutputStream(fileFullName);
			wb.write(out);
			out.close();
		}
		catch (FileNotFoundException e) {
			logger.error("{}",e);
		}
		catch (IOException e) {
			logger.error("{}",e);
		}
		return fileFullName;
	}



}
